#!/usr/bin/env python
# -*- coding: utf-8 -*-
import sys
from lib.format_time import *
from lib.HandleMysql import *
from lib.switch_code import *
from shop_conn import *
from cate_city_tools import *
from lib.load_data import *
import json
import sys
import re
import os
sys.setdefaultencoding('utf-8')

def set_ppc_conn():    
    conn = HandleMysql('newppc-s.db.58dns.org','58885','user_ppc_ro','0%wjd]YBLG','dbwww58com_newppc')
    conn.connect()
    return conn
def set_hy_conn():  
    conn = HandleMysql('10.5.20.15','5029','root','group_buytj1140','huangye')
    conn.connect()
    return conn

def set_hystats_conn():  
    conn = HandleMysql('10.5.20.16','58885','ec_stat','L#c8UM5RGh5C','dbwww58com_huangye_stats')
    conn.connect()
    return conn

def get_fundation_stats(date):
    shop_conn = set_shop_conn()
    conn = set_stats_conn()
    hy_conn = set_hy_conn()
    hys_conn = set_hystats_conn()
    ppc_conn = set_ppc_conn()
    
    month = get_date_info(date,'%Y%m')
    stat_table = 'ec_fundation_stats'
    line_list = []
    #,sum(a.pv),sum(a.uv)
    query = "SELECT b.cate1_id,b.cate1_name,b.cate_id,b.cate_name,b.city_id,b.city_name,sum(a.shop_pv)  FROM shop_flux_day a left join shop_info b on a.shop_id=b.shop_id where a.stat_date='%s' and b.cate_id>0 and b.city_id> 0 group by b.cate1_id,b.cate_id,b.city_id ;"%date
    #print query
    items = conn.get_results(query)
    for item in items:
        cate1_id = item[0]
        cate1_name = item[1]
        cate2_id = item[2]
        cate2_name = item[3]
        city_id = item[4]
        city_name = item[5]
        shop_pv = item[6]
        #total_uv = item[7]
        
        
        pay_shop_count = 0
        free_shop_count = 0        
        list_uv = 0
        list_pv = 0
        list_click = 0
        shop_uv_from_list = 0
        shop_pv_from_list = 0
        
        q = "select count(case when shop_type=1 then 1 end),count(case when shop_type=0 then 1 end) from shop_info where state=1 and date(open_time)<='%s' and city_id=%s and cate_id=%s"%(date,city_id,cate2_id)
        its = conn.get_results(q)
        if len(its) > 0:
            pay_shop_count = its[0][0]
            free_shop_count = its[0][1]
            
        online_product_count = 0
        new_product_count = 0
        q = "select count(1),count(case when date(public_time) ='%s' then 1 end) from product_info where cate2_name='%s' and city_id=%s and is_verify=1 and date(public_time) <='%s'"%(date,cate2_name,city_id,date)
        #print q
        its = conn.get_results(q)
        if len(its) > 0:
            online_product_count = its[0][0]
            new_product_count  = its[0][1]
            
        shop_total_uv = shop_pv
        #total_uv = 0
        #total_pv = 0
        
        shop_home_uv = 0
        #shop_home_uv = list_click
        product_uv = 0
        product_pv = 0
        q = "select sum(pv),sum(uv),sum(product_pv) from product_flux_day a left join product_info b  on a.product_id=b.product_id where a.stat_date='%s' and b.cate2_name='%s' and b.city_id=%s "%(date,cate2_name,city_id)
        #print q
        its = conn.get_results(q)
        if len(its) > 0:
            product_pv = its[0][0]
            product_uv = its[0][1]
            shop_home_uv = its[0][2]
            if product_uv == None:
                product_uv = 0
            if product_pv == None:
                product_pv = 0
            if shop_home_uv == None:
                shop_home_uv = 0
        
        q = "select ifnull(sum(click_count),0) from display_click_day_%s where stat_date='%s' and city1='%s' and cate2='%s' and product_type in ('jinpushangpin','dianpushangpin') "%(month,date,city_name,cate2_name)
        #print q
        try:
            its = hy_conn.get_results(q)
            if len(its) > 0:
                list_click = its[0][0]
        except Exception:
            pass
                
        
        list_pv = 0
        list_uv = 0
        q= "SELECT sum(pv),sum(uv) FROM `city_cate_report` where stat_date='%s' and product_type='整体' and city_name='%s' and  cate2='%s';"%(date,city_name,cate2_name)
        its = hys_conn.get_results(q)
        if len(its) > 0:
            list_pv = its[0][0]
            list_uv = its[0][1]
            #list_click = its[0][2] 
            
        if list_uv == None:
            list_uv = 0
        if list_pv == None:
            list_pv = 0
        #if list_click == None:
        #    list_click = 0
        
        shop_total_uv = shop_total_uv + product_uv
        shop_total_pv = list_click + product_pv
        if shop_total_uv < shop_total_pv:
            shop_total_pv = shop_total_pv + shop_total_uv
        
        shop_uv_from_list = product_uv
        shop_pv_from_list = list_click
        
        shop_other_uv = 0
        book_click = 0
        
        book_order_count = 0
        book_confirm_time_lenth = 0
        book_confirm_order_count  = 0 
        q = "select count(distinct a.order_id) as book_order_count,count(case when a.book_state=1 then 1 end) from order_book a left join product_info b  on a.product_id=b.product_id where date(a.operation_time)='%s' and b.cate2_name='%s' and b.city_id=%s "%(date,cate2_name,city_id)
        #print q
        its = conn.get_results(q)
        if len(its) > 0:
            book_order_count = its[0][0]
            #book_confirm_time_lenth = its[0][1]
            book_confirm_order_count = its[0][1]
            #if book_confirm_time_lenth == None:
            #    book_confirm_time_lenth = 0
            if book_confirm_order_count == None:
                book_confirm_order_count = 0
        
        q = "select avg(TIMESTAMPDIFF(second,b.create_time,a.operatedate)) from order_log a left join order_info b on a.order_id=b.order_id where date(a.operatedate)='%s' and b.city_id=%s and b.cate2_name='%s'"%(date,city_id,cate2_name)
        its = conn.get_results(q)
        if len(its) > 0:
            book_confirm_time_lenth = its[0][0]
        if book_confirm_time_lenth == None:
            book_confirm_time_lenth = 0
            
        pay_order_count = 0
        q = "select count(1) from order_info where order_state in (4,8) and date(create_time)='%s' and cate2_name='%s' and city_name='%s'"%(date,cate2_name,city_name)
        #print q
        its = conn.get_results(q)
        if len(its) > 0:
            pay_order_count = its[0][0]
        
        comment_count = 0
        q = "select count(1) from comment a left join shop_info b on a.shop_id=b.shop_id where date(a.comm_date)='%s' and b.cate_id=%s and b.city_id=%s"%(date,cate2_id,city_id)
        its = conn.get_results(q)
        if len(its) > 0:
            comment_count = its[0][0]
        
        total_400phone_count = 0
        valid_400phone_count = 0
        unlink_400phone_count = 0
        q= "select count(1),count(case when called_time>60 then 1 end),count(case when called_time=0 then 1 end) from t_cate_call_history a left join t_call_history b on a.call_history_id=b.id  where a.cate_id=%s and ani_city='%s' and date(add_date)='%s';"%(cate2_id,city_name,date)
        its = ppc_conn.get_results(q)
        if len(its) > 0:
            total_400phone_count = its[0][0]
            valid_400phone_count = its[0][1]
            unlink_400phone_count = its[0][2]
        
        param = [date,city_id,city_name,cate1_id,cate1_name,cate2_id,cate2_name,list_uv,list_pv,list_click,shop_uv_from_list,shop_pv_from_list,pay_shop_count,free_shop_count,online_product_count,new_product_count,shop_total_uv,shop_total_pv,shop_home_uv,product_uv,shop_other_uv,book_click,book_order_count,book_confirm_order_count,book_confirm_time_lenth,pay_order_count,comment_count,total_400phone_count,valid_400phone_count,unlink_400phone_count]
        line_list.append('\t'.join(map(lambda i : str(i), param)))
    
    cont = '\n'.join(line_list)
    cont = cont + '\n'
    path = "/work3/opt/analytic/lh_tmp_data/shop/fundation_stats/"
    file_name = "fundation_stats_%s.txt"%date.replace('-',"")
    writeTextToFile(path,file_name,"w",cont)
    
    q="delete from ec_fundation_stats where stat_date='%s'"%date
    conn.execute_sql(q)
    load_data(conn,path+file_name,stat_table,'\t')
    
    ppc_conn.close()
    conn.close()
    shop_conn.close()
    hys_conn.close()


def main():
   if len(sys.argv)==1:
        start_date=get_yesterday()
        end_date=start_date
   elif len(sys.argv)==2:
        start_date=format_date(sys.argv[1])
        end_date=start_date
   elif len(sys.argv)==3:
        start_date=format_date(sys.argv[1])
        end_date=format_date(sys.argv[2])

   stat_date=start_date
   while stat_date>=start_date and stat_date<=end_date:
        print "The program of the date of %s is beginning at %s " %(stat_date,get_now())

         #process data
        get_fundation_stats( stat_date) 
        print "The program of the date of %s is end at %s " %(stat_date,get_now())
        stat_date=var_date(stat_date)

if __name__=='__main__':
    print 'start'
    main()
